﻿#include "mydb.h"
#include "json11.hpp"
#include <iostream>
#include <list>
#include <map>
extern "C"
{
#include <string.h>
}

MyDB::MyDB()
{
    if (!InitDB(DB_PATH))
    {
        cout << "InitDB error" << endl;
        exit(-1);
    }
    cout << "db init success" << endl;
}

MyDB::~MyDB()
{
    if (!MyDB::ppDb)
    {
        // 关闭数据库
        sqlite3_close(ppDb);
    }
}

bool MyDB::InitDB(const char *db_path)
{
    // 1、打开数据库文件，如果文件不存在，则创建
    int ret = sqlite3_open(db_path, &ppDb);
    if (ret != SQLITE_OK)
    {
        cout << "open database error\n";
        return false;
    }
    // 2、创建表
    // 1）先准备创建表的sql语句
    char *errmsg;

    // 用户信息表
    const char *sql = "create table if not exists user_info("
                      "user_id varchar(32) PRIMARY KEY," // 用户id,注册时的时间戳
                      "username varchar(64),"            // 自定义的用户名
                      "pic_path varchar(128),"           // 照片路径
                      "rfid_card_no varchar(64),"        // rfid卡卡号
                      "create_time datetime);";          // 注册时间

    // 2)执行sql语句
    // 之前我们执行sql语句 借助的是QT中提供的数据库操作类来执行sql语句
    // 现在应该 使用 C语言数据库接口 来执行sql语句
    ret = sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg);
    if (ret != SQLITE_OK)
    {
        cout << "create user_info error:" << errmsg << endl;
        return false;
    }

    // 用户人脸库表,用于人脸检索时比对
    sql = "create table if not exists user_face_base("
          "user_id varchar(32),"        // 用户id,注册时的时间戳
          "face_pic_path varchar(128)," // 图片路径
          "create_time datetime)";      // 创建时间
    // 2)执行sql语句
    // 之前我们执行sql语句 借助的是QT中提供的数据库操作类来执行sql语句
    // 现在应该 使用 C语言数据库接口 来执行sql语句
    ret = sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg);
    if (ret != SQLITE_OK)
    {
        cout << "create user_face_base error:" << errmsg << endl;
        return false;
    }

    // 考勤记录表
    sql = "create table if not exists attendance_record("
          "user_id varchar(32),"        // 用户id,注册时的时间戳
          "att_way int,"                // 考勤方式(1:刷脸,2:刷卡)
          "face_pic_path varchar(128)," // 图片路径
          "create_time datetime)";      // 创建时间
    // 2)执行sql语句
    // 之前我们执行sql语句 借助的是QT中提供的数据库操作类来执行sql语句
    // 现在应该 使用 C语言数据库接口 来执行sql语句
    ret = sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg);
    if (ret != SQLITE_OK)
    {
        cout << "create attendance_record error:" << errmsg << endl;
        return false;
    }

    return true;
}

string *MyDB::selectData(string *sql, string *data)
{
    int ret;                 // 执行结果
    sqlite3_stmt *stmt = 0;  //
    int ncolumn = 0;         // 行数
    const char *column_name; // 字段名
    int vtype;               // 字段类型

    // 查询数据
    ret = sqlite3_prepare_v2(ppDb, sql->c_str(), sql->length(), &stmt, 0);
    if (ret != SQLITE_OK)
    {
        cout << "selectData error:" << sql << ":" << sqlite3_errmsg(ppDb) << endl;
        return NULL;
    }
    else if (stmt == NULL)
    {
        cout << "result is NULL\n";
        return NULL;
    }
    else
    {
        ncolumn = sqlite3_column_count(stmt);
        if (ncolumn == 0)
        {
            cout << "result column is 0\n";
            return NULL;
        }
        list<map<string, string>> list1;
        map<string, list<map<string, string>>> map2;

        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            map<string, string> map1;
            for (int i = 0; i < ncolumn; i++)
            {
                // vtype = sqlite3_column_type(stmt, i);
                column_name = sqlite3_column_name(stmt, i);
                if (column_name == NULL || strlen(column_name) == 0)
                {
                    continue;
                }

#if 0
                switch (vtype) {
                    case SQLITE_NULL: {
                        map1[column_name] = "";
                        // printf("%s: null\n", column_name);
                        break;
                    }
                    case SQLITE_INTEGER: {
                        map1[column_name] = to_string(sqlite3_column_int(stmt, i));
                        // printf("%s: %d\n", column_name, sqlite3_column_int(stmt, i));
                        break;
                    }
                    case SQLITE_FLOAT: {
                        map1[column_name] = to_string(sqlite3_column_double(stmt, i));
                        // printf("%s: %f\n", column_name, sqlite3_column_double(stmt, i));
                        break;
                    }
                    case SQLITE_BLOB: { /* arguably fall through... */
                        map1[column_name] = "BLOB";
                        // printf("%s: BLOB\n", column_name);
                        break;
                    }
                    case SQLITE_TEXT: {
                        map1[column_name] = (char *)sqlite3_column_text(stmt, i);
                        // printf("%s: %s\n", column_name, sqlite3_column_text(stmt, i));
                        break;
                    }
                    default: {
                        printf("%s: ERROR [%s]\n", column_name, sqlite3_errmsg(ppDb));
                        break;
                    }
                }
#endif
                char *value = (char *)sqlite3_column_text(stmt, i);
                if (value == NULL || strlen(value) == 0)
                {
                    map1[column_name] = "";
                }
                else
                {
                    map1[column_name] = (char *)sqlite3_column_text(stmt, i);
                }
            }
            list1.push_back(map1);
        }
        json11::Json json(list1);
        *data = json.dump();

        sqlite3_finalize(stmt);

        return data;
    }
}

bool MyDB::updateData(string *sql)
{
    char *errmsg;
    int ret = sqlite3_exec(ppDb, sql->c_str(), NULL, NULL, &errmsg);
    if (ret != SQLITE_OK)
    {
        cout << "updateData error:" << sql << ":" << errmsg << endl;
        return false;
    }
    return true;
}